/*
 * Copyright (c) 2023-2024 elsfs Authors. All Rights Reserved.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.elsfs.cloud.screw.query.dm;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;
import java.util.stream.Collectors;
import javax.sql.DataSource;
import org.elsfs.cloud.common.util.exception.QueryException;
import org.elsfs.cloud.common.util.lang.Assert;
import org.elsfs.cloud.common.util.lang.CollectionUtils;
import org.elsfs.cloud.common.util.lang.ExceptionUtils;
import org.elsfs.cloud.common.util.sql.JdbcUtils;
import org.elsfs.cloud.screw.constant.ScrewConstants;
import org.elsfs.cloud.screw.mapping.Mapping;
import org.elsfs.cloud.screw.metadata.Column;
import org.elsfs.cloud.screw.metadata.Database;
import org.elsfs.cloud.screw.metadata.PrimaryKey;
import org.elsfs.cloud.screw.query.AbstractDatabaseQuery;
import org.elsfs.cloud.screw.query.dm.model.DmColumnModel;
import org.elsfs.cloud.screw.query.dm.model.DmDatabaseModel;
import org.elsfs.cloud.screw.query.dm.model.DmPrimaryKeyModel;
import org.elsfs.cloud.screw.query.dm.model.DmTableModel;

/**
 * 达梦数据库查询
 *
 * @author zeng
 */
@SuppressWarnings("serial")
public class DmDataBaseQuery extends AbstractDatabaseQuery {
  private final ConcurrentMap<String, List<DmTableModel>> tablesMap = new ConcurrentHashMap<>();
  private static final String DM_QUERY_TABLE_SQL =
      """
      select
          ut.table_name TABLE_NAME,
          utc.comments COMMENTS
      from
              user_tables ut
      left join USER_TAB_COMMENTS utc
      on
              ut.table_name=utc.table_name""";

  // + "        case uc.data_type when 'INT' then uc.data_type when 'CLOB' then uc.data_type when
  // 'BLOB' then uc.data_type when 'INTEGER' then uc.data_type else
  // concat(concat(concat(uc.data_type, '('), uc.data_length), ')')  end case AS COLUMN_TYPE     ,
  //    "
  private static final String DM_QUERY_COLUMNS_SQL =
      """
select
        ut.table_name TABLE_NAME    ,
        uc.column_name COLUMN_NAME  ,
        case uc.data_type when 'CLOB' then uc.data_type when 'BLOB' then uc.data_type
        else  concat(concat(concat(uc.data_type, '('), uc.data_length), ')')  end case AS COLUMN_TYPE     ,
        uc.data_length COLUMN_LENGTH  ,
        uc.DATA_PRECISION  DATA_PRECISION,
        uc.DATA_SCALE DECIMAL_DIGITS,
        case uc.NULLABLE when 'Y' then '1' else '0' end case NULLABLE,
        uc.DATA_DEFAULT COLUMN_DEF,
        ucc.comments REMARKS
from
        user_tables ut
left join USER_TAB_COMMENTS utc
on
        ut.table_name=utc.table_name
left join user_tab_columns uc
on
        ut.table_name=uc.table_name
left join user_col_comments ucc
on
        uc.table_name =ucc.table_name
    and uc.column_name=ucc.column_name
where 1=1""";

  private static final String DM_QUERY_PK_SQL =
      """
      SELECT
      C.OWNER AS TABLE_SCHEM,
      C.TABLE_NAME          ,
      C.COLUMN_NAME         ,
      C.POSITION        AS KEY_SEQ ,
      C.CONSTRAINT_NAME AS PK_NAME
      FROM
              ALL_CONS_COLUMNS C,
              ALL_CONSTRAINTS K
      WHERE
              K.CONSTRAINT_TYPE = 'P'
          AND K.OWNER           = '%s'
          AND K.CONSTRAINT_NAME = C.CONSTRAINT_NAME
          AND K.TABLE_NAME      = C.TABLE_NAME
          AND K.OWNER           = C.OWNER
      """;

  /**
   * 构造函数
   *
   * @param dataSource {@link DataSource}
   */
  public DmDataBaseQuery(DataSource dataSource) {
    super(dataSource);
  }

  /**
   * 获取数据库
   *
   * @return {@link Database} 数据库信息
   */
  @Override
  public Database getDataBase() throws QueryException {
    DmDatabaseModel model = new DmDatabaseModel();
    // 当前数据库名称
    model.setDatabase(getSchema());
    return model;
  }

  /**
   * 获取达梦数据库的schema
   *
   * @return {@link String} 达梦数据库的schema信息
   */
  @Override
  public String getSchema() throws QueryException {
    return null;
  }

  /**
   * 获取达梦数据库的schema
   *
   * @return {@link String} 达梦数据库的schema信息
   */
  public String getSchemaBak() throws QueryException {
    try {
      String schema = null;
      ResultSet rs = getMetaData().getSchemas();
      while (rs.next()) {
        schema = rs.getString(1);
        break;
      }
      return schema;
    } catch (Exception e) {
      throw ExceptionUtils.mpe(e);
    }
  }

  /**
   * 获取表信息
   *
   * @return {@link List} 所有表信息
   */
  @Override
  public List<DmTableModel> getTables() throws QueryException {
    ResultSet resultSet = null;
    try {
      // 查询
      resultSet =
          getMetaData()
              .getTables(
                  getSchema(), getSchema(), ScrewConstants.PERCENT_SIGN, new String[] {"TABLE"});
      // 映射
      List<DmTableModel> list = Mapping.convertList(resultSet, DmTableModel.class);

      resultSet = prepareStatement(DM_QUERY_TABLE_SQL).executeQuery();
      List<DmTableModel> inquires = Mapping.convertList(resultSet, DmTableModel.class);

      // 处理备注信息
      list.forEach(
          (DmTableModel model) -> {
            // 备注
            inquires.stream()
                .filter(inquire -> model.getTableName().equals(inquire.getTableName()))
                .forEachOrdered(inquire -> model.setRemarks(inquire.getRemarks()));
          });
      if (!list.isEmpty()) {
        tablesMap.put("AllTable", list);
      }
      return list;
    } catch (SQLException e) {
      throw ExceptionUtils.mpe(e);
    } finally {
      JdbcUtils.close(resultSet, this.connection);
    }
  }

  /**
   * 获取列信息
   *
   * @param table {@link String} 表名
   * @return {@link List} 表字段信息
   */
  @Override
  public List<DmColumnModel> getTableColumns(String table) throws QueryException {
    Assert.notEmpty(table, "Table name can not be empty!");
    ResultSet resultSet = null;
    List<DmColumnModel> resultList = new ArrayList<>();
    List<String> tableNames = new ArrayList<>();
    try {
      // 从缓存中获取表对象
      List<DmTableModel> tables = tablesMap.get("AllTable");
      if (tables.isEmpty()) {
        tables = getTables();
      } else {
        for (DmTableModel dtm : tables) {
          tableNames.add(dtm.getTableName());
        }
      }
      /*如果表为空，则直接返回*/
      if (tableNames.isEmpty()) {
        return null;
      }

      if (CollectionUtils.isEmpty(columnsCaching)) {
        // 查询全部
        if (table.equals(ScrewConstants.PERCENT_SIGN)) {
          PreparedStatement statement = prepareStatement(DM_QUERY_COLUMNS_SQL);
          resultSet = statement.executeQuery();
        } else {
          // 查询单表的列信息
          String singleTableSql = DM_QUERY_COLUMNS_SQL.concat(" and ut.table_name='%s'");
          resultSet = prepareStatement(String.format(singleTableSql, table)).executeQuery();
        }

        List<DmColumnModel> inquires = Mapping.convertList(resultSet, DmColumnModel.class);
        // 这里利用lambda表达式将多行列信息按table name 进行归类，并放入缓存
        tableNames.forEach(
            name ->
                columnsCaching.put(
                    name,
                    inquires.stream()
                        .filter(i -> i.getTableName().equals(name))
                        .collect(Collectors.toList())));
        resultList = inquires;
      }
      return resultList;
    } catch (SQLException e) {
      throw ExceptionUtils.mpe(e);
    } finally {
      JdbcUtils.close(resultSet, this.connection);
    }
  }

  /**
   * 获取所有列信息
   *
   * @return {@link List} 表字段信息
   * @throws QueryException QueryException
   */
  @Override
  public List<? extends Column> getTableColumns() throws QueryException {
    return getTableColumns(ScrewConstants.PERCENT_SIGN);
  }

  /**
   * 根据表名获取主键
   *
   * @param table {@link String}
   * @return {@link List}
   * @throws QueryException QueryException
   */
  @Override
  public List<? extends PrimaryKey> getPrimaryKeys(String table) throws QueryException {
    ResultSet resultSet = null;
    try {
      // 查询
      resultSet = getMetaData().getPrimaryKeys(getSchema(), getSchema(), table);
      // 映射
      return Mapping.convertList(resultSet, DmPrimaryKeyModel.class);
    } catch (SQLException e) {
      throw ExceptionUtils.mpe(e);
    } finally {
      JdbcUtils.close(resultSet, this.connection);
    }
  }

  /**
   * 根据表名获取主键
   *
   * @return {@link List}
   * @throws QueryException QueryException
   */
  @Override
  public List<? extends PrimaryKey> getPrimaryKeys() throws QueryException {
    ResultSet resultSet = null;
    try {
      // 由于单条循环查询存在性能问题，所以这里通过自定义SQL查询数据库主键信息
      String sql = String.format(DM_QUERY_PK_SQL, getSchema());
      resultSet = prepareStatement(sql).executeQuery();
      return Mapping.convertList(resultSet, DmPrimaryKeyModel.class);
    } catch (SQLException e) {
      throw new QueryException(e);
    } finally {
      JdbcUtils.close(resultSet);
    }
  }
}
